﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mapeo;
using NHibernate;
using BE;
using NHibernate.Criterion;
using MySql.Data.MySqlClient;
using System.Data;

namespace DAL
{
    public class Horarios_DAL : Controlador
    {
        public DataTable ObtenerListaHorario(String CodigoEspecialidad, String Ciclo)
        {

            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ListadoHorario";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_Esp", CodigoEspecialidad);
                    SqlCmd.Parameters.AddWithValue("@in_ciclo", Ciclo);

                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }



        public void GenerarAsistencias(String semestre,String especialidad, String ciclo)
        {

            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "Cursor_CrearAsistenciasMoodle";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_semestre", semestre);
                    SqlCmd.Parameters.AddWithValue("@in_especialidad", especialidad);
                    SqlCmd.Parameters.AddWithValue("@in_ciclo", ciclo);
                    SqlCmd.ExecuteNonQuery();
                    Trans.Commit();
                }
            }
        }


        public DataTable ObtenerListaCursos(String semestre, String especialidad, String ciclo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ListadoCursosEspecialidadCiclo";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_semestre", semestre);
                    SqlCmd.Parameters.AddWithValue("@in_especialidad", especialidad);
                    SqlCmd.Parameters.AddWithValue("@in_ciclo", ciclo);


                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }





        public Horario ObtenerHorarioPorId(String ID)
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    Horario horario = (Horario)Sesion.CreateCriteria("Horario").Add(Restrictions.Eq("IDTabla", Convert.ToInt32(ID))).UniqueResult();
                    return horario;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }


        public void ActualizarCursoEnNota(String IDHorario, String IDCurso)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    try
                    {
                        MySqlCommand SqlCmd = new MySqlCommand();
                        SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                        Trans.Enlist(SqlCmd);

                        Horario cp = ObtenerHorarioPorId(IDHorario);
                        cp.IDCurso = Convert.ToInt64(IDCurso);

                        Sesion.Update(cp);
                        Sesion.Flush();


                        Trans.Commit();
                    }
                    catch (Exception)
                    {
                        Trans.Rollback();
                        throw;
                    }
                }
            }

        }

    }
}
